#!/usr/bin/python

# -*- coding: iso-8859-1 -*-

from sqlalchemy import *
from sqlalchemy.orm import *
from utils.connectionfactory import *
from model.todo import *

class ToDoDao:
    """ Database communication class """

##    file = 'sqlite:///todo.db'
    file = 'sqlite:////home/carlan/.pyToDo/todo.db'
    debug = True

    def __init__(self):
        """ Start database connection """    
        self.cf = ConnectionFactory(self.file, self.debug)
        self.con = self.cf.openConnection()

    def initTables(self):
        """ Start tables """
        self.todoTable = Table('todo', self.cf.metadata,
            Column('id', Integer, primary_key=True),
            Column('descricao',String,nullable=False),
            Column('comentario',String),
            Column('data',Integer,nullable=False),
            Column('importancia',Integer)
        )        
        #If not exists, do some work
        if not self.todoTable.exists():
            self.todoTable.create()
            
        self.todoTable = Table('todo', self.cf.metadata, autoload=True)
        #Map model to table
        self.todoMapper = mapper(ToDo, self.todoTable)

    def insert(self, toDoItem):
        """ Insert new To Do item with Session and RollBack """
        self.session = self.cf.openSession()
        self.transaction = self.session.create_transaction()
        try:            
            self.session.save(toDoItem)
            self.session.flush()
            self.session.clear()            
            self.transaction.commit()
        except:
            self.transaction.rollback()
            raise
        else:            
            self.session.close()

    def delete(self, id):
        """ Delete To Do item with Session and RollBack """        
        self.session = self.cf.openSession()               
        self.transaction = self.session.create_transaction()
        try:
            toDoItem = self.getById(id)
            self.session.delete(toDoItem)            
            self.session.flush()
            self.session.clear()            
            self.transaction.commit()
        except:
            self.transaction.rollback()
            raise
        else:                      
            self.session.close()

    def update(self, toDoItem):
        """ """
        self.session = self.cf.openSession()
        self.transaction = self.session.create_transaction()
        try:            
            self.session.update(toDoItem)
            self.session.flush()
            self.session.clear()            
            self.transaction.commit()
        except:
            self.transaction.rollback()
            raise
        else:            
            self.session.close()       

    def getQueryById(self, id):
        """ Get To Do Item """
        self.session = self.cf.openSession()
        return self.session.query(ToDo).get(id)

    def getById(self, id):
        """ Get To Do item """        
        self.session = self.cf.openSession()
        return self.session.load(ToDo, id)   
        
    def list(self):
        """ List To Do Itens """
        self.session = self.cf.openSession()
        return self.session.query(ToDo).select()
